﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using CommonLibrary.Data;

namespace CommonLibrary.Data.Sql
{
    public static class ClSql
    {
        #region ConnectionString

        #region ExecuteNonQuery

        public static int ExecuteNonQuery(string in_ConnectionString, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteNonQuery(in_ConnectionString, cmdType, in_sql, in_paras);
        }

        public static int ExecuteNonQuery(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteNonQuery(in_ConnectionString, cmdType, in_sql, null);
        }

        public static int ExecuteNonQuery(string in_ConnectionString, string in_sql, SqlParameter[] in_paras)
        {
            return ExecuteNonQuery(in_ConnectionString, CommandType.Text, in_sql, in_paras);
        }

        public static int ExecuteNonQuery(string in_ConnectionString, string in_sql)
        {
            return ExecuteNonQuery(in_ConnectionString, in_sql, null);
        }


        #endregion


        #region ExecuteScalar

        public static object ExecuteScalar(string in_ConnectionString, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteScalar(in_ConnectionString, cmdType, in_sql, in_paras);
        }

        public static object ExecuteScalar(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteScalar(in_ConnectionString, cmdType, in_sql, null);
        }

        public static object ExecuteScalar(string in_ConnectionString, string in_sql, SqlParameter[] in_paras)
        {
            return ExecuteScalar(in_ConnectionString, CommandType.Text, in_sql, in_paras);
        }

        public static object ExecuteScalar(string in_ConnectionString, string in_sql)
        {
            return ExecuteScalar(in_ConnectionString, in_sql, null);
        }

        #endregion


        #region ExecuteDataSet

        public static DataSet ExecuteDataSet(string in_ConnectionString, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteDataSet(in_ConnectionString, cmdType, in_sql, in_paras);
        }

        public static DataSet ExecuteDataSet(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteDataSet(in_ConnectionString, cmdType, in_sql, null);
        }

        public static DataSet ExecuteDataSet(string in_ConnectionString, string in_sql, SqlParameter[] in_paras)
        {
            return ExecuteDataSet(in_ConnectionString, CommandType.Text, in_sql, in_paras);
        }

        public static DataSet ExecuteDataSet(string in_ConnectionString, string in_sql)
        {
            return ExecuteDataSet(in_ConnectionString, in_sql, null);
        }


        #endregion


        #region ExecuteDataTable

        public static DataTable ExecuteDataTable(string in_ConnectionString, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            DataSet ds = SqlHelper.ExecuteDataSet(in_ConnectionString, cmdType, in_sql, in_paras);
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        public static DataTable ExecuteDataTable(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteDataTable(in_ConnectionString, cmdType, in_sql, null);
        }

        public static DataTable ExecuteDataTable(string in_ConnectionString, string in_sql, SqlParameter[] in_paras)
        {
            return ExecuteDataTable(in_ConnectionString, CommandType.Text, in_sql, in_paras);
        }

        public static DataTable ExecuteDataTable(string in_ConnectionString, string in_sql)
        {
            return ExecuteDataTable(in_ConnectionString, in_sql, null);
        }

        #endregion


        #region ExecuteReader 用完后一定要使用reader.Close

        public static SqlDataReader ExecuteReader(string in_ConnectionString, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteReader(in_ConnectionString, cmdType, in_sql, in_paras);
        }

        public static SqlDataReader ExecuteReader(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteReader(in_ConnectionString, cmdType, in_sql, null);
        }

        public static SqlDataReader ExecuteReader(string in_ConnectionString, string in_sql, SqlParameter[] in_paras)
        {
            return ExecuteReader(in_ConnectionString, CommandType.Text, in_sql, in_paras);
        }

        public static SqlDataReader ExecuteReader(string in_ConnectionString, string in_sql)
        {
            return ExecuteReader(in_ConnectionString, in_sql, null);
        }

        #endregion


        #region ExecuteNonQueryTransaction

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, CommandType cmdType, string in_sql, SqlParameter[] in_paras, bool in_commit)
        {
            using (SqlConnection cn = new SqlConnection(in_ConnectionString))
            {
                cn.Open();
                SqlTransaction tran = cn.BeginTransaction();

                int i = tran.ClExecuteNonQuery(cmdType, in_sql, in_paras);


                if ((i >= 0) && in_commit)
                {
                    tran.Commit();
                    return i;
                }

                tran.Rollback();
                return i;


            }
        }

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return ExecuteNonQueryTransaction(in_ConnectionString, cmdType, in_sql, in_paras, true);
        }

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, CommandType cmdType, string in_sql)
        {
            return ExecuteNonQueryTransaction(in_ConnectionString, cmdType, in_sql, null);
        }

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, string in_sql, SqlParameter[] in_paras, bool in_commit)
        {
            return ExecuteNonQueryTransaction(in_ConnectionString, CommandType.Text, in_sql, in_paras, in_commit);
        }

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, string in_sql, SqlParameter[] in_paras)
        {
            return ExecuteNonQueryTransaction(in_ConnectionString, in_sql, in_paras, true);
        }

        public static int ExecuteNonQueryTransaction(string in_ConnectionString, string in_sql)
        {
            return ExecuteNonQueryTransaction(in_ConnectionString, in_sql, null, true);

        }

        public static bool ExecuteNonQueryTransaction(string in_ConnectionString, Dictionary<string, SqlParameter[]> listParams)
        {
            using (SqlConnection cn = new SqlConnection(in_ConnectionString))
            {
                cn.Open();
                SqlTransaction tran = cn.BeginTransaction();
                try
                {
                    foreach (KeyValuePair<string, SqlParameter[]> kv in listParams)
                    {
                        tran.ClExecuteNonQuery(kv.Key, kv.Value);
                    }
                    tran.Commit();
                    return true;

                }
                catch
                {
                    tran.Rollback();
                    return false;
                }
                finally
                {
                    cn.Close();
                }

            }
        }

        #endregion


        #region CheckIsEmpty

        /// <summary>
        /// 判断SQL命令查询的结果是否存在
        /// </summary>
        /// <param name="in_sql"></param>
        /// <param name="in_paras"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(string in_ConnectionString, string in_sql, SqlParameter[] in_paras)
        {
            int counts = Convert.ToInt32(ExecuteScalar(in_ConnectionString, in_sql, in_paras));
            if (counts == 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 判断SQL命令查询的结果是否为空
        /// </summary>
        /// <param name="in_sql"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(string in_ConnectionString, string in_sql)
        {
            return CheckIsEmpty(in_ConnectionString, in_sql, null);
        }

        #endregion


        #region IsCanDelete

        /// <summary>
        /// 是否能删除数据
        /// </summary>
        /// <returns>true:能删除数据  ,false:不能删除数据</returns>
        public static bool IsCanDelete(string in_ConnectionString, string deleteSql, params SqlParameter[] cmdParms)
        {
            try
            {
                ExecuteNonQueryTransaction(in_ConnectionString, deleteSql, cmdParms, false);
                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion




        #region 临时表

        #region CreateTempTable
        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <returns>返回临时表名</returns>
        public static string CreateTempTable(string in_ConnectionString, string tableColumns, string suffix)
        {
            string strTempTN = SqlPublicDAL.GetTempTableName(suffix);

            string strSql = "IF OBJECT_ID('" + strTempTN + "') IS NOT NULL " +
                           "BEGIN " +
                           "DROP TABLE " + strTempTN + " " +
                           "END " +
                           "CREATE TABLE " + strTempTN + "(" + tableColumns + ")";

            ExecuteNonQuery(in_ConnectionString, strSql);

            return strTempTN;
        }
        #endregion

        #region IsTempTableExists
        /// <summary>
        /// 检查临时表是否存在
        /// </summary>
        /// <param name="tempTableName">临时表名</param>
        /// <returns></returns>
        public static bool IsTempTableExists(string in_ConnectionString, string tempTableName)
        {
            bool blExist = false;
            StringBuilder builderSql = new StringBuilder();
            builderSql.Append("IF OBJECT_ID('" + tempTableName + "') IS NOT  NULL ");
            builderSql.Append("BEGIN SELECT 1 END ");
            builderSql.Append("ELSE BEGIN SELECT 0 END ");
            object obj = ExecuteScalar(in_ConnectionString, builderSql.ToString());
            if (Convert.ToInt32(obj) > 0)
            {
                blExist = true;
            }
            return blExist;
        }
        #endregion

        #region DropTempTable

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="in_TempTableName"></param>
        /// <returns></returns>
        public static bool DropTempTable(string in_ConnectionString, string in_TempTableName)
        {
            try
            {
                ExecuteNonQuery(in_ConnectionString, "if object_id('" + in_TempTableName + "') is not null drop table " + in_TempTableName + " ");
                return true;
            }
            catch (Exception err)
            {
                FileClass.Log(err);
                throw;
            }

        }

        #endregion

        #endregion


        #region 将DataTable导入到SQL临时表

        /// <summary>
        /// 将DataTable导入到SQL临时表
        /// </summary>
        /// <param name="in_program"></param>
        /// <param name="in_stru"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string DataTableToSql(string in_ConnectionString, string in_program, string in_stru, DataTable dt)
        {
            using (SqlConnection cn = new SqlConnection(in_ConnectionString))
            {
                cn.Open();

                return cn.ClDataTableToSql(in_program, in_stru, dt);
            }
        }

        /// <summary>
        /// 将DataTable导入到SQL临时表
        /// </summary>
        /// <param name="in_stru"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string DataTableToSql(string in_ConnectionString, string in_stru, DataTable dt)
        {
            using (SqlConnection cn = new SqlConnection(in_ConnectionString))
            {
                cn.Open();

                return cn.ClDataTableToSql(in_stru, dt);
            }
        }

        /// <summary>
        /// 将DataTable导入到SQL临时表
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string DataTableToSql(string in_ConnectionString, DataTable dt)
        {
            using (SqlConnection cn = new SqlConnection(in_ConnectionString))
            {
                cn.Open();

                return cn.ClDataTableToSql(dt);
            }
        }

        #endregion


        #region GetPageData

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <param name="dropRowID">是否删除row_number排序号</param>
        /// <returns></returns>
        public static DataTable GetPageData(string in_ConnectionString, string in_Sql, SqlParameter[] in_paras, int in_PageSize, int in_PageNum, bool dropRowID, out int out_Rows, out int out_Pages)
        {
            using (SqlConnection cn = new SqlConnection(in_ConnectionString))
            {
                cn.Open();

                return cn.ClGetPageData(in_Sql, in_paras, in_PageSize, in_PageNum, dropRowID, out out_Rows, out out_Pages);
            }
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="in_ConnectionString"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <returns></returns>
        public static DataTable GetPageData(string in_ConnectionString, string in_Sql, SqlParameter[] in_paras, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return GetPageData(in_ConnectionString, in_Sql, in_paras, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }

        public static DataTable GetPageData(string in_ConnectionString, string in_Sql, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return GetPageData(in_ConnectionString, in_Sql, null, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }
        #endregion


        #region GetServerName
        /// <summary>
        /// 获取ServerName
        /// </summary>
        /// <returns></returns>
        public static string GetServerName(string in_ConnectionString)
        {
            string RsString = "select @@SERVERNAME ";
            return Convert.ToString(ExecuteScalar(in_ConnectionString, RsString));
        }

        #endregion


        #region GetServerTime
        /// <summary>
        /// 获取ServerName
        /// </summary>
        /// <returns></returns>
        public static string GetServerTime(string in_ConnectionString)
        {
            string RsString = "select convert(nvarchar(23), getdate(), 121) ";
            return Convert.ToString(ExecuteScalar(in_ConnectionString, RsString));
        }

        #endregion

        #endregion

        #region Connection

        #region ClFromFile

        /// <summary>
        /// 从XML文件获取连接字符串并创建连接
        /// </summary>
        /// <param name="in_cn"></param>
        /// <param name="in_XmlFile"></param>
        /// <param name="index"></param>
        /// <returns></returns>
        public static SqlConnection ClFromFile(this SqlConnection in_cn, string in_XmlFile, int index)
        {
            in_cn.ConnectionString = SqlPublicDAL.GetConnectionString(in_XmlFile, index);
            in_cn.Open();
            return in_cn;
        }

        /// <summary>
        /// 从XML文件获取连接字符串并创建连接
        /// </summary>
        /// <param name="in_cn"></param>
        /// <param name="in_XmlFile"></param>
        /// <returns></returns>
        public static SqlConnection ClFromFile(this SqlConnection in_cn, string in_XmlFile)
        {
            in_cn.ConnectionString = SqlPublicDAL.GetConnectionString(in_XmlFile);
            in_cn.Open();
            return in_cn;
        }

        /// <summary>
        /// 从XML文件获取连接字符串并创建连接
        /// </summary>
        /// <param name="in_cn"></param>
        /// <param name="in_XmlFile"></param>
        /// <param name="in_Description"></param>
        /// <returns></returns>
        public static SqlConnection ClFromFile(this SqlConnection in_cn, string in_XmlFile, string in_Description)
        {
            in_cn.ConnectionString = SqlPublicDAL.GetConnectionString(in_XmlFile, in_Description);
            in_cn.Open();
            return in_cn;
        }

        #endregion


        #region ClExecuteNonQuery

        public static int ClExecuteNonQuery(this SqlConnection cn, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteNonQuery(cn, cmdType, in_sql, in_paras);
        }

        public static int ClExecuteNonQuery(this SqlConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ClExecuteNonQuery(cmdType, in_sql, null);
        }

        public static int ClExecuteNonQuery(this SqlConnection cn, string in_sql, SqlParameter[] in_paras)
        {
            return cn.ClExecuteNonQuery(CommandType.Text, in_sql, in_paras);
        }

        public static int ClExecuteNonQuery(this SqlConnection cn, string in_sql)
        {
            return cn.ClExecuteNonQuery(in_sql, null);
        }

        #endregion


        #region ClExecuteScalar

        public static object ClExecuteScalar(this SqlConnection cn, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteScalar(cn, cmdType, in_sql, in_paras);
        }

        public static object ClExecuteScalar(this SqlConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ClExecuteScalar(cmdType, in_sql, null);
        }

        public static object ClExecuteScalar(this SqlConnection cn, string in_sql, SqlParameter[] in_paras)
        {
            return cn.ClExecuteScalar(CommandType.Text, in_sql, in_paras);
        }

        public static object ClExecuteScalar(this SqlConnection cn, string in_sql)
        {
            return cn.ClExecuteScalar(in_sql, null);
        }

        #endregion


        #region ClExecuteDataSet

        public static DataSet ClExecuteDataSet(this SqlConnection cn, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteDataSet(cn, cmdType, in_sql, in_paras);
        }

        public static DataSet ClExecuteDataSet(this SqlConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ClExecuteDataSet(cmdType, in_sql, null);
        }

        public static DataSet ClExecuteDataSet(this SqlConnection cn, string in_sql, SqlParameter[] in_paras)
        {
            return cn.ClExecuteDataSet(CommandType.Text, in_sql, in_paras);
        }

        public static DataSet ClExecuteDataSet(this SqlConnection cn, string in_sql)
        {
            return cn.ClExecuteDataSet(in_sql, null);
        }

        #endregion


        #region ClExecuteDataTable

        public static DataTable ClExecuteDataTable(this SqlConnection cn, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            DataSet ds = cn.ClExecuteDataSet(cmdType, in_sql, in_paras);
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        public static DataTable ClExecuteDataTable(this SqlConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ClExecuteDataTable(cmdType, in_sql, null);
        }

        public static DataTable ClExecuteDataTable(this SqlConnection cn, string in_sql, SqlParameter[] in_paras)
        {
            return cn.ClExecuteDataTable(CommandType.Text, in_sql, in_paras);
        }

        public static DataTable ClExecuteDataTable(this SqlConnection cn, string in_sql)
        {
            return cn.ClExecuteDataTable(in_sql, null);
        }

        #endregion


        #region ExecuteReader 用完后一定要使用reader.Close

        public static SqlDataReader ExecuteReader(this SqlConnection cn, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteReader(cn, cmdType, in_sql, in_paras);
        }

        public static SqlDataReader ExecuteReader(this SqlConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ExecuteReader(cmdType, in_sql, null);
        }

        public static SqlDataReader ExecuteReader(this SqlConnection cn, string in_sql, SqlParameter[] in_paras)
        {
            return cn.ExecuteReader(CommandType.Text, in_sql, in_paras);
        }

        public static SqlDataReader ExecuteReader(this SqlConnection cn, string in_sql)
        {
            return cn.ExecuteReader(in_sql, null);
        }

        #endregion


        #region ClExecuteNonQueryTransaction

        public static int ClExecuteNonQueryTransaction(this SqlConnection cn, CommandType cmdType, string in_sql, SqlParameter[] in_paras, bool in_commit)
        {
            SqlTransaction tran = cn.BeginTransaction();

            int i = tran.ClExecuteNonQuery(cmdType, in_sql, in_paras);


            if ((i >= 0) && in_commit)
            {
                tran.Commit();
                return i;
            }

            tran.Rollback();
            return i;

        }

        public static int ClExecuteNonQueryTransaction(this SqlConnection cn, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return cn.ClExecuteNonQueryTransaction(cmdType, in_sql, in_paras, true);
        }

        public static int ClExecuteNonQueryTransaction(this SqlConnection cn, CommandType cmdType, string in_sql)
        {
            return cn.ClExecuteNonQueryTransaction(cmdType, in_sql, null);
        }

        public static int ClExecuteNonQueryTransaction(this SqlConnection cn, string in_sql, SqlParameter[] in_paras, bool in_commit)
        {
            return cn.ClExecuteNonQueryTransaction(CommandType.Text, in_sql, in_paras, in_commit);
        }

        public static int ClExecuteNonQueryTransaction(this SqlConnection cn, string in_sql, SqlParameter[] in_paras)
        {
            return cn.ClExecuteNonQueryTransaction(in_sql, in_paras, true);
        }

        public static int ClExecuteNonQueryTransaction(this SqlConnection cn, string in_sql)
        {
            return cn.ClExecuteNonQueryTransaction(in_sql, null, true);

        }

        public static bool ClExecuteNonQueryTransaction(this SqlConnection cn, Dictionary<string, SqlParameter[]> listParams)
        {
            SqlTransaction tran = cn.BeginTransaction();
            try
            {
                foreach (KeyValuePair<string, SqlParameter[]> kv in listParams)
                {
                    tran.ClExecuteNonQuery(kv.Key, kv.Value);
                }
                tran.Commit();
                return true;

            }
            catch
            {
                tran.Rollback();
                return false;
            }
            finally
            {
                cn.Close();
            }
        }

        #endregion


        #region CheckIsEmpty

        /// <summary>
        /// 判断SQL命令查询的结果是否存在
        /// </summary>
        /// <param name="in_sql"></param>
        /// <param name="in_paras"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(this SqlConnection cn, string in_sql, SqlParameter[] in_paras)
        {
            int counts = Convert.ToInt32(cn.ClExecuteScalar(in_sql, in_paras));
            if (counts == 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 判断SQL命令查询的结果是否为空
        /// </summary>
        /// <param name="in_sql"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(this SqlConnection cn, string in_sql)
        {
            return cn.CheckIsEmpty(in_sql, null);
        }

        #endregion


        #region IsCanDelete

        /// <summary>
        /// 是否能删除数据
        /// </summary>
        /// <returns>true:能删除数据  ,false:不能删除数据</returns>
        public static bool IsCanDelete(this SqlConnection cn, string deleteSql, params SqlParameter[] cmdParms)
        {
            try
            {
                cn.ClExecuteNonQueryTransaction(deleteSql, cmdParms, false);
                return true;
            }
            catch
            {
                return false;
            }
        }

        #endregion




        #region 临时表

        #region CreateTempTable
        /// <summary>
        /// 创建临时表
        /// </summary>
        /// <returns>返回临时表名</returns>
        public static string CreateTempTable(this SqlConnection cn, string tableColumns, string suffix)
        {
            string strTempTN = SqlPublicDAL.GetTempTableName(suffix);

            string strSql = "IF OBJECT_ID('" + strTempTN + "') IS NOT NULL " +
                           "BEGIN " +
                           "DROP TABLE " + strTempTN + " " +
                           "END " +
                           "CREATE TABLE " + strTempTN + "(" + tableColumns + ")";

            cn.ClExecuteNonQuery(strSql);

            return strTempTN;
        }
        #endregion


        #region IsTempTableExists
        /// <summary>
        /// 检查临时表是否存在
        /// </summary>
        /// <param name="tempTableName">临时表名</param>
        /// <returns></returns>
        public static bool IsTempTableExists(this SqlConnection cn, string tempTableName)
        {
            bool blExist = false;
            StringBuilder builderSql = new StringBuilder();
            builderSql.Append("IF OBJECT_ID('" + tempTableName + "') IS NOT  NULL ");
            builderSql.Append("BEGIN SELECT 1 END ");
            builderSql.Append("ELSE BEGIN SELECT 0 END ");
            object obj = cn.ClExecuteScalar(builderSql.ToString());
            if (Convert.ToInt32(obj) > 0)
            {
                blExist = true;
            }
            return blExist;
        }
        #endregion


        #region DropTempTable

        /// <summary>
        /// 删除临时表
        /// </summary>
        /// <param name="in_TempTableName"></param>
        /// <returns></returns>
        public static bool DropTempTable(this SqlConnection cn, string in_TempTableName)
        {
            try
            {
                cn.ClExecuteNonQuery("if object_id('" + in_TempTableName + "') is not null drop table " + in_TempTableName + " ");
                return true;
            }
            catch (Exception err)
            {
                FileClass.Log(err);
                throw;
            }

        }

        #endregion

        #endregion


        #region 将DataTable导入到SQL临时表

        /// <summary>
        /// 将DataTable导入到SQL临时表
        /// </summary>
        /// <param name="in_program"></param>
        /// <param name="in_stru"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string ClDataTableToSql(this SqlConnection cn, string in_program, string in_stru, DataTable dt)
        {
            string RsString = "";


            SqlCommand cmd = new SqlCommand();
            cmd.CommandTimeout = 3600 * 24;
            cmd.Connection = cn;

            string tempTableName = SqlPublicDAL.GetTempTableName(in_program);

            RsString = "if object_id('" + tempTableName + "') is not null drop table " + tempTableName + " " +
               "create table " + tempTableName + "(" + in_stru + ")";
            cmd.CommandText = RsString;
            cmd.CommandType = CommandType.Text;
            cmd.ExecuteNonQuery();

            SqlBulkCopy bcp = new SqlBulkCopy(cn);
            bcp.BulkCopyTimeout = 36000;
            bcp.DestinationTableName = tempTableName;//目标表，必须存在
            bcp.WriteToServer(dt);//往里面插入数据，不是同步比较是否一样，而是直接插数据，类似bcp,insert的操作
            bcp.Close();

            return tempTableName;
        }

        /// <summary>
        /// 将DataTable导入到SQL临时表
        /// </summary>
        /// <param name="in_stru"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string ClDataTableToSql(this SqlConnection cn, string in_stru, DataTable dt)
        {
            return cn.ClDataTableToSql(dt.TableName, in_stru, dt);
        }

        /// <summary>
        /// 将DataTable导入到SQL临时表
        /// </summary>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static string ClDataTableToSql(this SqlConnection cn, DataTable dt)
        {
            if (dt == null || dt.Rows.Count == 0)
            {
                return null;
            }

            string stru = "";
            foreach (DataColumn column in dt.Columns)
            {
                string type = "";
                if (column.DataType.ToString() == "System.String")
                {
                    type = "nvarchar(max)";
                }

                if (column.DataType.ToString() == "System.Int32")
                {
                    type = "int";
                }
                if (column.DataType.ToString() == "System.Int64")
                {
                    type = "bigint";
                }

                if (column.DataType.ToString() == "System.Boolean")
                {
                    type = "bit";
                }

                if (column.DataType.ToString() == "System.Double")
                {
                    type = "decimal(38, 20)";
                }

                if (column.DataType.ToString() == "System.DateTime")
                {
                    type = "nvarchar(max)";
                }

                if (column.DataType.ToString() == "System.Guid")
                {
                    type = "uniqueidentifier";
                }

                stru += "[" + column.Caption + "] " + type;

                if (column != dt.Columns[dt.Columns.Count - 1])
                {
                    stru += ", ";
                }


            }

            return cn.ClDataTableToSql(stru, dt);

        }

        #endregion


        #region GetPageData

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <param name="dropRowID">是否删除row_number排序号</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this SqlConnection cn, string in_Sql, SqlParameter[] in_paras, int in_PageSize, int in_PageNum, bool dropRowID, out int out_Rows, out int out_Pages)
        {
            int m_PageSize = in_PageSize;
            if (m_PageSize <= 0)
            {
                m_PageSize = 10;
            }
            int m_PageNum = in_PageNum;
            if (m_PageNum <= 0)
            {
                m_PageNum = 1;
            }

            string m_Sql = in_Sql.ClMatch(@".*(?=Order By)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            string m_OrderBy = in_Sql.ClMatch("Order By.*", RegexOptions.IgnoreCase | RegexOptions.Singleline);

            string m_Cte =
@"with CTE
as (
     select row_number() over({0}) as row_number, *
     from   
     (
       {1}
     ) st
)
";
            m_Cte = m_Cte.ClFormat(m_OrderBy, m_Sql);

            string RsString = m_Cte + "select count(1) from CTE";
            out_Rows = Convert.ToInt32(cn.ClExecuteScalar(RsString, in_paras));

            out_Pages = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(out_Rows) / Convert.ToDouble(m_PageSize)));

            RsString = m_Cte + "select * from CTE where row_number > {0} and row_number <= {1} ";
            RsString = RsString.ClFormat(m_PageSize * (m_PageNum - 1), m_PageSize * m_PageNum);

            DataTable dataTable = cn.ClExecuteDataTable(RsString, in_paras);

            if (dropRowID)
            {
                dataTable.Columns.Remove("row_number");
            }
            return dataTable;
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <param name="dropRowID">是否删除row_number排序号</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this SqlConnection cn, string in_Sql, SqlParameter[] in_paras, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return cn.ClGetPageData(in_Sql, in_paras, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <param name="dropRowID">是否删除row_number排序号</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this SqlConnection cn, string in_Sql, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return cn.ClGetPageData(in_Sql, null, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }
        #endregion


        #region GetServerName
        /// <summary>
        /// 获取ServerName
        /// </summary>
        /// <returns></returns>
        public static string GetServerName(this SqlConnection cn)
        {
            string RsString = "select @@SERVERNAME ";
            return Convert.ToString(cn.ClExecuteScalar(RsString));
        }

        #endregion


        #region GetServerTime
        /// <summary>
        /// 获取ServerName
        /// </summary>
        /// <returns></returns>
        public static string GetServerTime(this SqlConnection cn)
        {
            string RsString = "select convert(nvarchar(23), getdate(), 121) ";
            return Convert.ToString(cn.ClExecuteScalar(RsString));
        }

        #endregion

        #endregion

        #region Tran

        #region ClExecuteNonQuery

        public static int ClExecuteNonQuery(this SqlTransaction tran, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteNonQuery(tran, cmdType, in_sql, in_paras);
        }

        public static int ClExecuteNonQuery(this SqlTransaction tran, CommandType cmdType, string in_sql)
        {
            return tran.ClExecuteNonQuery(cmdType, in_sql, null);
        }

        public static int ClExecuteNonQuery(this SqlTransaction tran, string in_sql, SqlParameter[] in_paras)
        {
            return tran.ClExecuteNonQuery(CommandType.Text, in_sql, in_paras);
        }

        public static int ClExecuteNonQuery(this SqlTransaction tran, string in_sql)
        {
            return tran.ClExecuteNonQuery(in_sql, null);
        }

        #endregion


        #region ClExecuteScalar

        public static object ClExecuteScalar(this SqlTransaction tran, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteScalar(tran, cmdType, in_sql, in_paras);
        }

        public static object ClExecuteScalar(this SqlTransaction tran, CommandType cmdType, string in_sql)
        {
            return tran.ClExecuteScalar(cmdType, in_sql, null);
        }

        public static object ClExecuteScalar(this SqlTransaction tran, string in_sql, SqlParameter[] in_paras)
        {
            return tran.ClExecuteScalar(CommandType.Text, in_sql, in_paras);
        }

        public static object ClExecuteScalar(this SqlTransaction tran, string in_sql)
        {
            return tran.ClExecuteScalar(in_sql, null);
        }

        #endregion


        #region ClExecuteDataSet

        public static DataSet ClExecuteDataSet(this SqlTransaction tran, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteDataSet(tran, cmdType, in_sql, in_paras);
        }

        public static DataSet ClExecuteDataSet(this SqlTransaction tran, CommandType cmdType, string in_sql)
        {
            return tran.ClExecuteDataSet(cmdType, in_sql, null);
        }

        public static DataSet ClExecuteDataSet(this SqlTransaction tran, string in_sql, SqlParameter[] in_paras)
        {
            return tran.ClExecuteDataSet(CommandType.Text, in_sql, in_paras);
        }

        public static DataSet ClExecuteDataSet(this SqlTransaction tran, string in_sql)
        {
            return tran.ClExecuteDataSet(in_sql, null);
        }


        #endregion


        #region ClExecuteDataTable

        public static DataTable ClExecuteDataTable(this SqlTransaction tran, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            DataSet ds = tran.ClExecuteDataSet(cmdType, in_sql, in_paras);
            if (ds.Tables.Count == 0)
            {
                return null;
            }
            return ds.Tables[0];
        }

        public static DataTable ClExecuteDataTable(this SqlTransaction tran, CommandType cmdType, string in_sql)
        {
            return tran.ClExecuteDataTable(cmdType, in_sql, null);
        }

        public static DataTable ClExecuteDataTable(this SqlTransaction tran, string in_sql, SqlParameter[] in_paras)
        {
            return tran.ClExecuteDataTable(CommandType.Text, in_sql, in_paras);
        }

        public static DataTable ClExecuteDataTable(this SqlTransaction tran, string in_sql)
        {
            return tran.ClExecuteDataTable(in_sql, null);
        }

        #endregion


        #region ExecuteReader 用完后一定要使用reader.Close

        public static SqlDataReader ExecuteReader(this SqlTransaction tran, CommandType cmdType, string in_sql, SqlParameter[] in_paras)
        {
            return SqlHelper.ExecuteReader(tran, cmdType, in_sql, in_paras);
        }

        public static SqlDataReader ExecuteReader(this SqlTransaction tran, CommandType cmdType, string in_sql)
        {
            return tran.ExecuteReader(cmdType, in_sql, null);
        }

        public static SqlDataReader ExecuteReader(this SqlTransaction tran, string in_sql, SqlParameter[] in_paras)
        {
            return tran.ExecuteReader(CommandType.Text, in_sql, in_paras);
        }

        public static SqlDataReader ExecuteReader(this SqlTransaction tran, string in_sql)
        {
            return tran.ExecuteReader(in_sql, null);
        }

        #endregion


        #region CheckIsEmpty

        /// <summary>
        /// 判断SQL命令查询的结果是否存在
        /// </summary>
        /// <param name="in_sql"></param>
        /// <param name="in_paras"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(this SqlTransaction tran, string in_sql, SqlParameter[] in_paras)
        {
            int counts = Convert.ToInt32(tran.ClExecuteScalar(in_sql, in_paras));
            if (counts == 0)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 判断SQL命令查询的结果是否为空
        /// </summary>
        /// <param name="in_sql"></param>
        /// <returns></returns>
        public static bool CheckIsEmpty(this SqlTransaction tran, string in_sql)
        {
            return tran.CheckIsEmpty(in_sql, null);
        }

        #endregion


        #region GetPageData

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <param name="dropRowID">是否删除row_number排序号</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this SqlTransaction tran, string in_Sql, SqlParameter[] in_paras, int in_PageSize, int in_PageNum, bool dropRowID, out int out_Rows, out int out_Pages)
        {
            int m_PageSize = in_PageSize;
            if (m_PageSize <= 0)
            {
                m_PageSize = 10;
            }
            int m_PageNum = in_PageNum;
            if (m_PageNum <= 0)
            {
                m_PageNum = 1;
            }

            string m_Sql = in_Sql.ClMatch(@".*(?=Order By)", RegexOptions.IgnoreCase | RegexOptions.Singleline);
            string m_OrderBy = in_Sql.ClMatch("Order By.*", RegexOptions.IgnoreCase | RegexOptions.Singleline);

            string m_Cte =
@"with CTE
as (
     select row_number() over({0}) as row_number, *
     from   
     (
       {1}
     ) st
)
";
            m_Cte = m_Cte.ClFormat(m_OrderBy, m_Sql);

            string RsString = m_Cte + "select count(1) from CTE";
            out_Rows = Convert.ToInt32(tran.ClExecuteScalar(RsString, in_paras));

            out_Pages = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(out_Rows) / Convert.ToDouble(m_PageSize)));

            RsString = m_Cte + "select * from CTE where row_number > {0} and row_number <= {1} ";
            RsString = RsString.ClFormat(m_PageSize * (m_PageNum - 1), m_PageSize * m_PageNum);

            DataTable dataTable = tran.ClExecuteDataTable(RsString, in_paras);

            if (dropRowID)
            {
                dataTable.Columns.Remove("row_number");
            }
            return dataTable;
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_paras">sql参数</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <param name="dropRowID">是否删除row_number排序号</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this SqlTransaction tran, string in_Sql, SqlParameter[] in_paras, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return tran.ClGetPageData(in_Sql, in_paras, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }

        /// <summary>
        /// 分页处理
        /// </summary>
        /// <param name="cn"></param>
        /// <param name="in_Sql">sql命令</param>
        /// <param name="in_PageSize">一页取多少行</param>
        /// <param name="in_PageNum">取第几页</param>
        /// <param name="out_Rows">返回总行数</param>
        /// <param name="out_Pages">返回总页数</param>
        /// <param name="dropRowID">是否删除row_number排序号</param>
        /// <returns></returns>
        public static DataTable ClGetPageData(this SqlTransaction tran, string in_Sql, int in_PageSize, int in_PageNum, out int out_Rows, out int out_Pages)
        {
            return tran.ClGetPageData(in_Sql, null, in_PageSize, in_PageNum, true, out out_Rows, out out_Pages);
        }
        #endregion

        #endregion

    }
}
